using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using emp.Model;
using emp.IDAL;
using System.Collections;
using emp.Model.SysManage;

namespace emp.SqlServerDAL
{
    public class Item :  IItem
    {
        // Static constants
        private const string SQL_SELECT_ITEM_CHILDLIST = "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel FROM item_tbl  where isdel=@IsDel and Parent_Item_ID=@Parent_Item_ID";
        private const string SQL_SELECT_ITEM = "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel FROM item_tbl  where isdel=@IsDel and item_id=@Item_ID";
        private const string SQL_SELECT_ITEM_LIST = "SELECT Item_ID,Parent_Item_ID,Item_Name,Item_Desc,IsDel FROM item_tbl where isdel=@IsDel";
        private const string SQL_INSERT_ITEM = "INSERT INTO item_tbl VALUES(@Parent_Item_ID,@Item_Name,@Item_Desc,'0')";
        private const string SQL_UPDATE_ITEM = "UPDATE item_tbl SET Parent_Item_ID = @Parent_Item_ID, Item_Name = @Item_Name, Item_Desc = @Item_Desc WHERE Item_ID = @Item_ID";
        private const string SQL_DELETE_ITEM = "UPDATE item_tbl SET ISDEL = @IsDel WHERE Item_ID = @Item_ID";
        private const string PARM_ITEM_ID = "@Item_ID";
        private const string PARM_PARENT_ITEM_ID = "@Parent_Item_ID";
        private const string PARM_ITEM_NAME = "@Item_Name";
        private const string PARM_ITEM_DESC = "@Item_Desc";
        private const string PARM_ISDEL = "@IsDel";

        /// <summary>
        /// Insert a new account info the database
        /// </summary>
        /// <param name="acc">A thin data class containing all the new account information</param>
        public void InsertNode(ItemInfo acc)
        {
            //SqlParameter[] itemParms = GetItemParameters();
            //SetItemParameters(itemParms, acc);

            SqlParameter[] itemParms = null;


            string sql = " INSERT INTO item_tbl VALUES(" + acc.Parent_Node_ID + ",'" + acc.Node_Name + "','" + acc.Node_Desc + "','0')";

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        System.Console.WriteLine(SQL_INSERT_ITEM);
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, itemParms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(err.Message);
                    }
                }
            }
        }

        /// <summary>
        /// Update an account in the database
        /// </summary>
        /// <param name="myAccount">Updated account parameters, you must supply all parameters</param>
        public void ModifyNode(ItemInfo myItem)
        {
            SqlParameter[] itemParms = GetItemParametersID();

            SetItemParametersID(itemParms, myItem);


            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE_ITEM, itemParms);

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        public void DeleteNode(int node_id)
        {
            SqlParameter[] itemParms = GetItemDeleteParameters();

            itemParms[0].Value = node_id;
            itemParms[1].Value = "1";

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_DELETE_ITEM, itemParms);

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        public IList getChildNodeList(string isdel, int parent_node_id)
        {
            IList nodelist = new ArrayList();
            SqlParameter[] parm = new SqlParameter[]{
                new SqlParameter(PARM_PARENT_ITEM_ID, SqlDbType.Int, 4,"Parent_Item_ID"),
                new SqlParameter(PARM_ISDEL, SqlDbType.Char, 1,"IsDel")
            };
            parm[0].Value = parent_node_id;
            parm[1].Value = isdel;

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_ITEM_CHILDLIST, parm))
            {
                while (rdr.Read())
                {
                    TreeInfo nodeinfo = new TreeInfo(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
                    nodelist.Add(nodeinfo);
                }
            }

            return nodelist;
        }      

        public IList getCurrNode(int nodeid)
        {
            IList nodelist = new ArrayList();
            SqlParameter[] parm = new SqlParameter[]{
                new SqlParameter(PARM_ITEM_ID, SqlDbType.Int, 4,"Item_ID"),
                new SqlParameter(PARM_ISDEL, SqlDbType.Char, 1,"IsDel")
            };
            parm[0].Value = nodeid;
            parm[1].Value = "0";

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_ITEM, parm))
            {
                while (rdr.Read())
                {
                    TreeInfo nodeinfo = new TreeInfo(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
                    nodelist.Add(nodeinfo);
                }
            }

            return nodelist;
        }

        public string ConvertItem(int item_id)
        {
            string returnvalue = "";
            IList itemlist = (IList)this.getCurrNode(item_id);
            IEnumerator enumerator = itemlist.GetEnumerator();
            enumerator.MoveNext();
            returnvalue = ((TreeInfo)enumerator.Current).Node_Name;
            return returnvalue;
        }

        public TreeInfo getCurrTreeInfo(int nodeid)
        {
            TreeInfo nodeinfo=new TreeInfo();
            SqlParameter[] parm = new SqlParameter[]{
                new SqlParameter(PARM_ITEM_ID, SqlDbType.Int, 4,"Item_ID"),
                new SqlParameter(PARM_ISDEL, SqlDbType.Char, 1,"IsDel")
            };
            parm[0].Value = nodeid;
            parm[1].Value = "0";

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_ITEM, parm))
            {
                while (rdr.Read())
                {
                    nodeinfo = new TreeInfo(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
                   
                }
            }

            return nodeinfo;
        }


        /// <summary>
        /// An internal function to get the database parameters
        /// </summary>
        /// <returns>Parameter array</returns>
        private static SqlParameter[] GetItemParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT_ITEM);

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARM_PARENT_ITEM_ID, SqlDbType.Int, 4),
					new SqlParameter(PARM_ITEM_NAME, SqlDbType.VarChar, 100),
					new SqlParameter(PARM_ITEM_DESC, SqlDbType.VarChar, 100)};


                SQLHelper.CacheParameters(SQL_INSERT_ITEM, parms);
            }

            return parms;
        }

        /// <summary>
        /// An internal function to get the database parameters
        /// </summary>
        /// <returns>Parameter array</returns>
        private static SqlParameter[] GetItemDeleteParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_DELETE_ITEM);

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARM_ITEM_ID, SqlDbType.Int, 4),
					new SqlParameter(PARM_ISDEL, SqlDbType.Char, 1)};

                SQLHelper.CacheParameters(SQL_DELETE_ITEM, parms);
            }

            return parms;
        }

        /// <summary>
        /// An internal function to get the database parameters
        /// </summary>
        /// <returns>Parameter array</returns>
        private static SqlParameter[] GetItemParametersID()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT_ITEM);

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter(PARM_ITEM_ID, SqlDbType.Int, 4),
					new SqlParameter(PARM_PARENT_ITEM_ID, SqlDbType.Int, 4),
					new SqlParameter(PARM_ITEM_NAME, SqlDbType.VarChar, 100),
					new SqlParameter(PARM_ITEM_DESC, SqlDbType.VarChar, 100)};


                SQLHelper.CacheParameters(SQL_INSERT_ITEM, parms);
            }

            return parms;
        }



        /// <summary>
        /// An internal function to bind values parameters 
        /// </summary>
        /// <param name="parms">Database parameters</param>
        /// <param name="acc">Values to bind to parameters</param>
        private void SetItemParameters(SqlParameter[] parms, ItemInfo acc)
        {
            parms[0].Value = acc.Parent_Node_ID;
            parms[1].Value = acc.Node_Name;
            parms[2].Value = acc.Node_Desc;
        }

        /// <summary>
        /// An internal function to bind values parameters 
        /// </summary>
        /// <param name="parms">Database parameters</param>
        /// <param name="acc">Values to bind to parameters</param>
        private void SetItemParametersID(SqlParameter[] parms, ItemInfo acc)
        {
            parms[0].Value = acc.Node_ID;
            parms[1].Value = acc.Parent_Node_ID;
            parms[2].Value = acc.Node_Name;
            parms[3].Value = acc.Node_Desc;
        }

        public int getItemIDByDesc(string item_desc, int parent_item_id)
        {
            int returnvalue = 1;
            string SQL_ITEM_DESC = "select item_id from item_tbl where item_desc='" + item_desc + "' and parent_item_id=" + parent_item_id;
            SqlParameter[] parm = new SqlParameter[] { };
            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_ITEM_DESC, parm))
            {
                while (rdr.Read())
                {
                    returnvalue = rdr.GetInt32(0);
                }
            }
            return returnvalue;
        }

        
    }
}
